DROP DATABASE IF EXISTS `DFT`;
CREATE DATABASE `DFT` ;

USE `DFT`;

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users`(
    `userId` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `fullName` VARCHAR(200) COLLATE UTF8_BIN NOT NULL,
    `email` VARCHAR(200) COLLATE UTF8_BIN NOT NULL,
    `password` VARCHAR(45) COLLATE UTF8_BIN NOT NULL,
    `registrationDt` TIMESTAMP NULL DEFAULT NULL,
    `lastlogin` TIMESTAMP NULL DEFAULT NULL,
    `lastUpdatedDt` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`userId`),
    UNIQUE KEY `email_UNIQUE` (`email`)
) ENGINE=INNODB AUTO_INCREMENT=0 DEFAULT CHARSET=UTF8 COLLATE =UTF8_BIN;

DROP TABLE IF EXISTS `profiles`;
CREATE TABLE `profiles`(
    `profId` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `userId` BIGINT(20) NOT NULL,
    `parentProfId` BIGINT(20) DEFAULT NULL,
    `firstName` VARCHAR(45) DEFAULT NULL,
    `lastName` VARCHAR(45) DEFAULT NULL,
    `address` TEXT,
    `birthDate` VARCHAR(45) DEFAULT NULL,
    `gender` VARCHAR(10) DEFAULT NULL,
    `isDeath` VARCHAR(10) DEFAULT NULL,
    `deathDt` VARCHAR(45) DEFAULT NULL,
    `socialStatus` VARCHAR(10) DEFAULT NULL,
    `acadimicQual` VARCHAR(10) DEFAULT NULL,
    `ceatedDT` TIMESTAMP NULL DEFAULT NULL,
    `lastUpdatedDT` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `country` VARCHAR(45) DEFAULT NULL,
    `city` VARCHAR(45) DEFAULT NULL,
    `blodType` VARCHAR(10) DEFAULT NULL,
    `religion` VARCHAR(10) DEFAULT NULL,
    `image` MEDIUMBLOB,
    `imageType` VARCHAR(45) DEFAULT NULL,
    PRIMARY KEY (`profId`),
    KEY `FK_Users_Profiles` (`userId`),
    KEY `FK_Profiles_Profiles` (`parentProfId`),
    CONSTRAINT `FK_Profiles_Profiles` FOREIGN KEY (`parentProfId`)
        REFERENCES `profiles` (`profId`)
        ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT `FK_Users_Profilies` FOREIGN KEY (`userId`)
        REFERENCES `users` (`userId`)
        ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=INNODB AUTO_INCREMENT=0 DEFAULT CHARSET=UTF8;

DROP TABLE IF EXISTS `relations`;
CREATE TABLE `relations`(
    `relationId` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `userId` BIGINT(20) DEFAULT NULL,
    `relationuserId` BIGINT(20) DEFAULT NULL,
    `createdDT` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`relationId`),
    KEY `FK_Relations_Users` (`userId`),
    KEY `FK_Relations_RelationUsers` (`relationuserId`),
    CONSTRAINT `FK_Relations_RelationUsers` FOREIGN KEY (`relationuserId`)
        REFERENCES `users` (`userId`)
        ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT `FK_Relations_Users` FOREIGN KEY (`userId`)
        REFERENCES `users` (`userId`)
        ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=INNODB AUTO_INCREMENT=0 DEFAULT CHARSET=UTF8 COLLATE =UTF8_BIN;


DROP PROCEDURE IF EXISTS `AddRelationUser`;
DELIMITER $$
CREATE PROCEDURE `AddRelationUser`(IN p_userId BIGINT,IN p_relationUserId BIGINT)
BEGIN
    INSERT INTO `relations`(`userId`,`relationuserId`,`createdDT`)
    VALUES(p_userId,p_relationUserId,CURRENT_TIMESTAMP);
END$$
DELIMITER

DROP PROCEDURE IF EXISTS `ChangePasswordRequest`;
DELIMITER $$
CREATE PROCEDURE `ChangePasswordRequest`(IN p_userId BIGINT,IN p_oldPassword VARCHAR(45),IN p_newPassword VARCHAR(45), OUT statusCode VARCHAR(2))
BEGIN
    IF MD5(p_oldPassword) = (SELECT `password` FROM `users` WHERE `userId` = p_userId) THEN
        SET statusCode = '0';
        UPDATE `users`
        SET `password` = MD5(p_newPassword)
        WHERE `userId` = p_userId;
    ELSE 
        SET statusCode = '1';
    END IF;
END$$
DELIMITER 

DROP PROCEDURE IF EXISTS `CreateNewUser`;
DELIMITER $$
CREATE PROCEDURE `CreateNewUser`(IN p_fullName VARCHAR(200),IN p_email VARCHAR(200),IN p_pass VARCHAR(45), OUT statusCode VARCHAR(2))
BEGIN
    IF p_email IN (SELECT `email` FROM `users`) THEN
        SET statusCode = '1';
    ELSE 
        INSERT INTO `users`(`fullName`,`email`,`password`,`registrationDt`,`lastlogin`)
        VALUES(p_fullName,p_email,MD5(p_pass),CURRENT_TIMESTAMP,CURRENT_TIMESTAMP);
        SET statusCode = '0';
    END IF;
END$$
DELIMITER

DROP PROCEDURE IF EXISTS `CreateProfileUser`;
DELIMITER $$
CREATE PROCEDURE `CreateProfileUser`(IN p_userId BIGINT,IN p_parentProfId BIGINT,IN p_oldRootProfId BIGINT,IN p_firstName VARCHAR(45) , IN p_lastName VARCHAR(45),IN p_address TEXT,IN p_birthDate VARCHAR(45),IN p_gender VARCHAR(10),IN p_isDeath VARCHAR(10),IN p_deathDt VARCHAR(45),IN p_socialStatus VARCHAR(10),IN p_acadimicQual VARCHAR(10),IN p_country VARCHAR(45),IN p_city VARCHAR(45),IN p_blodType VARCHAR(10),IN p_religion VARCHAR(10),IN p_image MEDIUMBLOB,IN p_imageType VARCHAR(45))
BEGIN
    DECLARE rowcount INT;					
    INSERT INTO `profiles`
        (`userId`,`parentProfId`,`firstName`,`lastName`,`address`,`birthDate`,`gender`,`isDeath`,`deathDt`,
         `socialStatus`,`acadimicQual`,`ceatedDT`,`country`,`city`,`blodType`,`religion`,`image`,`imageType`)
    VALUES
        (p_userId,p_parentProfId,p_firstName,p_lastName,p_address,p_birthDate,p_gender,p_isDeath,p_deathDt,
         p_socialStatus,p_acadimicQual,CURRENT_TIMESTAMP,p_country,p_city,p_blodType,p_religion,p_image,p_imageType);
    SELECT ROW_COUNT() INTO ROWCOUNT;
    IF (p_oldRootProfId!= -1 AND  rowcount>0) THEN
        UPDATE `profiles`
        SET
        `parentProfId` = LAST_INSERT_ID()
        WHERE `profId` = p_oldRootProfId;
    END IF;
END$$
DELIMITER

DROP PROCEDURE IF EXISTS `GetAllProfilesUser`;
DELIMITER $$
CREATE PROCEDURE `GetAllProfilesUser`(IN p_userId BIGINT(20))
BEGIN
    SELECT `profId`,`parentProfId`,`firstName`,`lastName`,`gender`,`isDeath`
    FROM `profiles`
    WHERE `userId` = p_userId;
END$$
DELIMITER

DROP PROCEDURE IF EXISTS `GetAllRelationsUser`;
DELIMITER $$
CREATE PROCEDURE `GetAllRelationsUser`(IN p_userId BIGINT)
BEGIN
    SELECT `relations`.`relationId`, `users`.`userId`,`users`.`fullName` 
    FROM `users`,`relations` 
    WHERE `relations`.`userId` = p_userId AND `relations`.`relationuserId` = `users`.`userId`;
END$$
DELIMITER

DROP PROCEDURE IF EXISTS `GetAllStatisticsUser`;
DELIMITER $$
CREATE PROCEDURE `GetAllStatisticsUser`(IN p_userId BIGINT)
BEGIN
    SELECT "Total" AS "Column","All" AS "Value",COUNT(*) AS Total FROM  `profiles` WHERE `userId`= p_userId
    UNION
    SELECT "gender",`gender`,COUNT(*) FROM `profiles` WHERE `userId`= p_userId  GROUP BY `gender` 
    UNION 
    SELECT "isDeath",`isDeath` , COUNT(*) FROM `profiles` WHERE `userId`= p_userId GROUP BY `isDeath`
    UNION
    SELECT "socialStatus",  `socialStatus` , COUNT(*) FROM `profiles` WHERE `userId`= p_userId GROUP BY `socialStatus`
    UNION 
    SELECT "acadimicQual",`acadimicQual` , COUNT(*) FROM `profiles` WHERE `userId`= p_userId GROUP BY `acadimicQual`
    UNION
    SELECT "blodType",  `blodType` , COUNT(*) FROM `profiles` WHERE `userId`= p_userId GROUP BY `blodType` 
    UNION 
    SELECT "religion",`religion` , COUNT(*) FROM `profiles` WHERE `userId`= p_userId GROUP BY `religion`;
END$$
DELIMITER

DROP PROCEDURE IF EXISTS `GetAllTrees`;
DELIMITER $$
CREATE PROCEDURE `GetAllTrees`(IN p_userId BIGINT)
BEGIN
    SELECT `userId`,`fullName` FROM `users` WHERE `userId` != p_userId;
END$$
DELIMITER

DROP PROCEDURE IF EXISTS `GetImage`;
DELIMITER $$
CREATE PROCEDURE `GetImage`(IN p_profId BIGINT(20))
BEGIN
    SELECT `image`,`imageType` FROM `profiles` WHERE `profId` = p_profId;
END$$
DELIMITER

DROP PROCEDURE IF EXISTS `GetProfileDetails`;
DELIMITER $$
CREATE PROCEDURE `GetProfileDetails`(IN p_profId BIGINT(20))
BEGIN
    SELECT * FROM `profiles` WHERE `profId` = p_profId;
END$$
DELIMITER

DROP PROCEDURE IF EXISTS `RemoveProfileUser`;
DELIMITER $$
CREATE PROCEDURE `RemoveProfileUser`(IN p_profId BIGINT)
BEGIN
    DELETE FROM `profiles` WHERE `profId` = p_profId;
END$$
DELIMITER

DROP PROCEDURE IF EXISTS `RemoveRelationUser`;
DELIMITER $$
CREATE PROCEDURE `RemoveRelationUser`(IN p_relationId BIGINT)
BEGIN
    DELETE FROM `relations` WHERE `relationId` = p_relationId;
END$$
DELIMITER

DROP PROCEDURE IF EXISTS `ResetPasswordRequest`;
DELIMITER $$
CREATE PROCEDURE `ResetPasswordRequest`(IN p_email VARCHAR(200),IN p_newPassword VARCHAR(45), OUT p_fullName VARCHAR(200), OUT statusCode VARCHAR(2))
BEGIN
    IF LOWER(p_email) IN (SELECT LOWER(`email`) FROM `users`) THEN
        SET statusCode = '0';
        UPDATE `users`
        SET `password` = MD5(p_newPassword)
        WHERE LOWER(`email`)= LOWER(p_email);
        SELECT `fullName` INTO p_fullName FROM `users` WHERE LOWER(`email`)= LOWER(p_email);
    ELSE 
        SET statusCode = '1';
    END IF;
END$$
DELIMITER

DROP PROCEDURE IF EXISTS `UpdateProfileUser`;
DELIMITER $$
CREATE PROCEDURE `UpdateProfileUser`(IN p_profId BIGINT,IN p_firstName VARCHAR(45) , IN p_lastName VARCHAR(45),IN p_address TEXT,IN p_birthDate VARCHAR(45),IN p_gender VARCHAR(10),IN p_isDeath VARCHAR(10),IN p_deathDt VARCHAR(45),IN p_socialStatus VARCHAR(10),IN p_acadimicQual VARCHAR(10),IN p_country VARCHAR(45),IN p_city VARCHAR(45),IN p_blodType VARCHAR(10),IN p_religion VARCHAR(10),IN p_image MEDIUMBLOB,IN p_imageType VARCHAR(45))
BEGIN
    IF(p_imageType != '-1')THEN
        UPDATE `profiles`
        SET
        `firstName`= p_firstName,
        `lastName`= p_lastName,
        `address`= p_address,
        `birthDate`= p_birthDate,
        `gender`= p_gender,
        `isDeath`= p_isDeath,
        `deathDt`= p_deathDt,
        `socialStatus`= p_socialStatus,
        `acadimicQual`= p_acadimicQual,
        `country`= p_country,
        `city`= p_city,
        `blodType`= p_blodType,
        `religion`= p_religion,
        `image`= p_image,
        `imageType`= p_imageType
        WHERE `profId`= p_profId;
    ELSE
        UPDATE `profiles`
        SET
        `firstName`= p_firstName,
        `lastName`= p_lastName,
        `address`= p_address,
        `birthDate`= p_birthDate,
        `gender`= p_gender,
        `isDeath`= p_isDeath,
        `deathDt`= p_deathDt,
        `socialStatus`= p_socialStatus,
        `acadimicQual`= p_acadimicQual,
        `country`= p_country,
        `city`= p_city,
        `blodType`= p_blodType,
        `religion`= p_religion
        WHERE `profId`= p_profId;
    END IF;
END$$
DELIMITER

DROP PROCEDURE IF EXISTS `UpdateUserData`;
DELIMITER $$
CREATE PROCEDURE `UpdateUserData`(IN p_userId BIGINT, IN p_newFullName VARCHAR(200),IN p_newEmail VARCHAR(200),OUT statusCode VARCHAR(2))
BEGIN
    IF LOWER(p_newEmail) IN (SELECT LOWER(`email`) FROM `users` WHERE `userId` != p_userId) THEN
        SET statusCode = '1';
    ELSE 
        UPDATE `users`
        SET `fullName`= p_newFullName,`email`= p_newEmail
        WHERE `userId` = p_userId;
        SET statusCode = '0';
    END IF;
END$$
DELIMITER

DROP PROCEDURE IF EXISTS `ValidateUser`;
DELIMITER $$
CREATE PROCEDURE `ValidateUser`(IN p_email VARCHAR(200),IN p_pass VARCHAR(45))
BEGIN
    DECLARE statusCode VARCHAR(2);
    IF LOWER(p_email) IN (SELECT LOWER(`email`) FROM `users`) THEN
        IF LOWER(p_email) IN (SELECT LOWER(`email`) FROM `users` WHERE (LOWER(`email`)= LOWER(p_email) AND `password` = MD5(p_pass) )) THEN
            SET statusCode = '0';
            SELECT`userId`,`fullName`,`email`,`registrationDt`,`lastlogin` ,statusCode
            FROM `users` 
            WHERE (LOWER(`email`)= LOWER(p_email) AND `password` = MD5(p_pass));
            UPDATE `users`
            SET `lastlogin` = CURRENT_TIMESTAMP
            WHERE LOWER(`email`)= LOWER(p_email);
        ELSE 
            SET statusCode = '2';
            SELECT statusCode; 
        END IF;
    ELSE
        SET statusCode = '1';
        SELECT statusCode; 
    END IF;
END$$
DELIMITER
;